package cn.iocoder.yudao.module.data.service.ds;


import cn.iocoder.yudao.module.data.controller.admin.dscategory.dto.ColDto;
import cn.iocoder.yudao.module.data.dal.dataobject.dscategory.DsCategoryDO;
import cn.iocoder.yudao.module.data.dal.mysql.dscategory.DsCategoryMapper;
import cn.iocoder.yudao.module.data.service.dscategory.DsCategoryService;
import jakarta.annotation.Resource;
import org.springframework.stereotype.Service;
import org.springframework.validation.annotation.Validated;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.*;

@Service
@Validated
public class DsServiceImpl {


    @Resource
    private DsCategoryService dsCategoryService;


    //执行sql的方法
    public Map<String,Object> executeSql(String sql, Long dsId) {
        // 校验sql，保证sql只是查询语句
        if (!sql.trim().toLowerCase().startsWith("select")) {
            throw new IllegalArgumentException("仅支持查询语句，禁止执行其他类型的SQL");
        }

        Map<String,Object> map = new HashMap<String,Object>();

        // 防止SQL注入的额外检查 - 检测常见的SQL注入模式
        String sqlLower = sql.toLowerCase();
        if (sqlLower.contains(";") ||
                sqlLower.contains("--") ||
                sqlLower.contains("/*") ||
                sqlLower.contains("*/") ||
                sqlLower.matches(".*\\b(union|insert|update|delete|drop|alter)\\b.*")) {
            throw new IllegalArgumentException("检测到潜在的SQL注入风险，请检查SQL语句");
        }

        //sql限制查询条数(todo 后续可以优化)
        sql = sql + " limit 100";

        //根据dsId获取数据库连接
        // 这里可以根据dsId查询数据库连接信息
        DsCategoryDO dsCategory = dsCategoryService.getDsCategory(dsId);
        if (dsCategory == null) {
            throw new IllegalArgumentException("数据源不存在");
        }
        //根据dbInfoDto获取数据库连接
        String configuration = dsCategory.getConfiguration();

//        try {
//            //加载驱动
//            Class.forName("com.mysql.cj.jdbc.Driver");
//        } catch (ClassNotFoundException e) {
//            e.printStackTrace();
//        }

        try (Connection connection = DriverManager.getConnection(configuration)) {
            //执行sql,返回查询结果
            // 这里可以使用PreparedStatement来执行SQL语句
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            // 执行查询
            ResultSet resultSet = preparedStatement.executeQuery();
            // 处理结果集,将数据集形成一个二维数组
//            List<String> colTypes = new ArrayList<>();
//            List<String> cols = new ArrayList<>();
            List<ColDto> cols = new ArrayList<>();
            Map<String,List<Object>> data = new LinkedHashMap<>();
            // 获取列数
            int columnCount = resultSet.getMetaData().getColumnCount();
            for (int i = 1; i <= columnCount; i++) {
                ColDto colDto = new ColDto();
                String name = resultSet.getMetaData().getColumnName(i);
                colDto.setName(name);
                colDto.setType(resultSet.getMetaData().getColumnTypeName(i));
                String comment = resultSet.getMetaData().getColumnLabel(i);
                colDto.setComment(comment);
                cols.add(colDto);
                data.put(name, new ArrayList<>());
            }
            while (resultSet.next()) {
                List<Object> row = new ArrayList<>();
                for (int i = 1; i <= columnCount; i++) {
                    row.add(resultSet.getObject(i));
                    // 将每一行数据放入map中
                    String name = resultSet.getMetaData().getColumnName(i);
                    List<Object> list = data.get(name);
                    if (list != null) {
                        list.add(resultSet.getObject(i));
                    }
                }
                // 将每一行数据放入map中

            }

            // 将结果集放入map中
            map.put("cols", cols);
            map.put("data", data);

        } catch (Exception e) {
            e.printStackTrace();
        }

        return map;
    }
}
